package com.ets.NxtNmbr_DAO;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import com.ets.common.DateUtils;
import com.ets.database.DB_Pipe;

/*******************************************************************************
 The <big><b>NxtNmbrDB</b></big> class implements the reader & writer methods of
 the <b>NxtNmbrDAO</b> interface to provide the data access to the
 <big><b>NxtNmbr</b></big> table
 <br><br>
 @author D.Merkling - 1:33:38 PM - Aug 22, 2011
 <br><br>
 @version 1.2  
*******************************************************************************/
public class NxtNmbrDB extends DB_Pipe implements NxtNmbrDAO
  {
   public NxtNmbrDB ()
	 {
	 }
   
   /*******************************************************************************
    NxtNmbr reader methods
   *******************************************************************************/
   
   public NxtNmbr getNxtNmbr (String Sys)
	 {
	  String tmpSys = Sys.toUpperCase();
             tmpSys = tmpSys.trim();
      String sel = "SELECT Sys, "                            +
                          "Desc01, NN01, Desc02, NN02, Desc03, NN03, Desc04, NN04, " +
                          "Desc05, NN05, Desc06, NN06, Desc07, NN07, Desc08, NN08, " +
                          "Desc09, NN09, Desc10, NN10, Desc11, NN11, Desc12, NN12, " +
                          "Desc13, NN13, Desc14, NN14, Desc15, NN15, Desc16, NN16 "  +
                     "FROM nxt_nmbr "                        +
                    "WHERE Sys  = ? "                        +
                    "ORDER BY Sys  ASC";
	  try
		{
         DB_Pipe              = DB_Connect();
	     PreparedStatement ps = DB_Pipe.prepareStatement(sel);
		 ps.setString(1, tmpSys);
		 ResultSet rs = ps.executeQuery();
		 if (rs.next())
		   {
		    String Desc01 = rs.getString("Desc01");
	        int    NN01   = rs.getInt("NN01");
		    String Desc02 = rs.getString("Desc02");
	        int    NN02   = rs.getInt("NN02");
		    String Desc03 = rs.getString("Desc03");
	        int    NN03   = rs.getInt("NN03");
		    String Desc04 = rs.getString("Desc04");
	        int    NN04   = rs.getInt("NN04");
		    String Desc05 = rs.getString("Desc05");
	        int    NN05   = rs.getInt("NN05");
		    String Desc06 = rs.getString("Desc06");
	        int    NN06   = rs.getInt("NN06");
		    String Desc07 = rs.getString("Desc07");
	        int    NN07   = rs.getInt("NN07");
		    String Desc08 = rs.getString("Desc08");
	        int    NN08   = rs.getInt("NN08");
		    String Desc09 = rs.getString("Desc09");
	        int    NN09   = rs.getInt("NN09");
		    String Desc10 = rs.getString("Desc10");
	        int    NN10   = rs.getInt("NN10");
		    String Desc11 = rs.getString("Desc11");
	        int    NN11   = rs.getInt("NN11");
		    String Desc12 = rs.getString("Desc12");
	        int    NN12   = rs.getInt("NN12");
		    String Desc13 = rs.getString("Desc13");
	        int    NN13   = rs.getInt("NN13");
		    String Desc14 = rs.getString("Desc14");
	        int    NN14   = rs.getInt("NN14");
		    String Desc15 = rs.getString("Desc15");
	        int    NN15   = rs.getInt("NN15");
		    String Desc16 = rs.getString("Desc16");
	        int    NN16   = rs.getInt("NN16");
		    NxtNmbr nn = new NxtNmbr(Sys, Desc01, NN01, Desc02, NN02, Desc03, NN03, Desc04, NN04,   
                                          Desc05, NN05, Desc06, NN06, Desc07, NN07, Desc08, NN08, 
                                          Desc09, NN09, Desc10, NN10, Desc11, NN11, Desc12, NN12, 
                                          Desc13, NN13, Desc14, NN14, Desc15, NN15, Desc16, NN16); 
		    rs.close();
		    ps.close();
		    return nn;
		   }
		 else
		   return null;
		}
	  catch(SQLException sqle)
		{
		 //sqle.printStackTrace();	// for debugging
		 return null;
		}
	  finally
		{
		 DB_Disconnect(DB_Pipe);
		}
	 }
   
   public ArrayList<NxtNmbr> getNxtNmbrs ()
	 {
      String qry = "SELECT Sys, "        +
                          "Desc01, NN01, Desc02, NN02, Desc03, NN03, Desc04, NN04, " +
                          "Desc05, NN05, Desc06, NN06, Desc07, NN07, Desc08, NN08, " +
                          "Desc09, NN09, Desc10, NN10, Desc11, NN11, Desc12, NN12, " +
                          "Desc13, NN13, Desc14, NN14, Desc15, NN15, Desc16, NN16 "  +
			         "FROM nxt_nmbr "                                                +
		            "ORDER BY Sys  ASC";
	  try
		{
	     DB_Pipe                = DB_Connect();
		 ArrayList<NxtNmbr> nns = new ArrayList<NxtNmbr>();
		 PreparedStatement  ps  = DB_Pipe.prepareStatement(qry);
		 ResultSet          rs  = ps.executeQuery();
		 while(rs.next())
		   {
		    String Sys    = rs.getString("Sys");
		    String Desc01 = rs.getString("Desc01");
		    int    NN01   = rs.getInt("NN01");
		    String Desc02 = rs.getString("Desc02");
		    int    NN02   = rs.getInt("NN02");
		    String Desc03 = rs.getString("Desc03");
		    int    NN03   = rs.getInt("NN03");
		    String Desc04 = rs.getString("Desc04");
		    int    NN04   = rs.getInt("NN04");
		    String Desc05 = rs.getString("Desc05");
		    int    NN05   = rs.getInt("NN05");
		    String Desc06 = rs.getString("Desc06");
		    int    NN06   = rs.getInt("NN06");
		    String Desc07 = rs.getString("Desc07");
		    int    NN07   = rs.getInt("NN07");
		    String Desc08 = rs.getString("Desc08");
		    int    NN08   = rs.getInt("NN08");
		    String Desc09 = rs.getString("Desc09");
		    int    NN09   = rs.getInt("NN09");
		    String Desc10 = rs.getString("Desc10");
		    int    NN10   = rs.getInt("NN10");
		    String Desc11 = rs.getString("Desc11");
		    int    NN11   = rs.getInt("NN11");
		    String Desc12 = rs.getString("Desc12");
		    int    NN12   = rs.getInt("NN12");
		    String Desc13 = rs.getString("Desc13");
		    int    NN13   = rs.getInt("NN13");
		    String Desc14 = rs.getString("Desc14");
		    int    NN14   = rs.getInt("NN14");
		    String Desc15 = rs.getString("Desc15");
		    int    NN15   = rs.getInt("NN15");
		    String Desc16 = rs.getString("Desc16");
		    int    NN16   = rs.getInt("NN16");
		    NxtNmbr nn = new NxtNmbr(Sys, Desc01, NN01, Desc02, NN02, Desc03, NN03, Desc04, NN04,
                                          Desc05, NN05, Desc06, NN06, Desc07, NN07, Desc08, NN08,
                                          Desc09, NN09, Desc10, NN10, Desc11, NN11, Desc12, NN12,
                                          Desc13, NN13, Desc14, NN14, Desc15, NN15, Desc16, NN16);
		    nns.add(nn);
		   }
		 rs.close();
		 ps.close();
		 return nns;
		}
	  catch(SQLException sqle)
		{
		 //sqle.printStackTrace();  // for debugging
		 return null;
		}
	  finally
		{
		 DB_Disconnect(DB_Pipe);
		}
	 }

   /*******************************************************************************
    NxtNmbr writer methods
   *******************************************************************************/
   
   public int getNN (String Sys, int Which)
     {
	  String tmpSys   = Sys.toUpperCase();
	         tmpSys   = tmpSys.trim();
      int    tmpWhich = 0;
      int    tmpNN    = 0;
      if (Which < 1)
        {
         tmpWhich = 1;
        }
      else
        {
    	 if (Which > 16)
    	   {
    		tmpWhich = 16; 
    	   }
        }
	  String colNN = "";
	  int    valNN = 0;
      switch (tmpWhich)
        {
         case  1:
   	       colNN   = "NN01";
 	       break;
         case  2:
 	       colNN   = "NN02";
 	       break;
         case  3:
 	       colNN   = "NN03";
 	       break;
         case  4:
 	       colNN   = "NN04";
 	       break;
         case  5:
 	       colNN   = "NN05";
 	       break;
         case  6:
 	       colNN   = "NN06";
 	       break;
         case  7:
 	       colNN   = "NN07";
 	       break;
         case  8:
 	       colNN   = "NN08";
 	       break;
         case  9:
 	       colNN   = "NN09";
 	       break;
         case 10:
 	       colNN   = "NN10";
 	       break;
         case 11:
 	       colNN   = "NN11";
 	       break;
         case 12:
 	       colNN   = "NN12";
 	       break;
         case 13:
 	       colNN   = "NN13";
 	       break;
         case 14:
 	       colNN   = "NN14";
 	       break;
         case 15:
 	       colNN   = "NN15";
 	       break;
         case 16:
 	       colNN   = "NN16";
 	       break;
        }
     String selNxtNmbr = "SELECT Sys, "         +
                                 colNN   + " "  +
                           "FROM nxt_nmbr "     +
                          "WHERE Sys  = ? "     +
                          "ORDER BY Sys ASC";
	  try
		{
         DB_Pipe              = DB_Connect();
		 PreparedStatement ps = DB_Pipe.prepareStatement(selNxtNmbr);
		 ps.setString(1, tmpSys);
		 ResultSet rs = ps.executeQuery();
		 if (rs.next())
		   {
	        tmpNN = rs.getInt(colNN);
		    rs.close();
		    ps.close();
		   }
		 else
		   return 0;
		}
	  catch(SQLException sqle)
		{
		 //sqle.printStackTrace();	// for debugging
		 return 0;
		}
	  finally
		{
		 DB_Disconnect(DB_Pipe);
		}
	  valNN         = tmpNN + 1;
      String update = "UPDATE nxt_nmbr "           +
                         "SET " + colNN + " = ?, " +
                              "TMS_Updt     = ?, " +
		  	                  "User_Updt    = ? "  +
                       "WHERE Sys  = ? "           +
		  	             "AND " + colNN + " = ?";
      try
        {
         DB_Pipe              = DB_Connect();
		 PreparedStatement ps = DB_Pipe.prepareStatement(update);
	     Timestamp         ts = DateUtils.getTmstmp();
		 ps.setInt(1, valNN);
		 ps.setTimestamp(2, ts);
		 ps.setString(3, "USER8888");
		 ps.setString(4, tmpSys);
		 ps.setInt(5, tmpNN);
		 ps.executeUpdate();
		 ps.close();
		 return valNN;
		}
	  catch(SQLException sqle)
		{
		 //sqle.printStackTrace();	// for debugging
	 	 return 0;
		}
	  finally
		{
		 DB_Disconnect(DB_Pipe);
		}
     }

   public boolean clearNxtNmbr (String Sys, int Which)
	 {
	  String tmpSys   = Sys.toUpperCase();
             tmpSys   = tmpSys.trim();
      int    tmpWhich = 0;
      if (Which < 1)
        {
         tmpWhich = 1;
        }
      else
        {
    	 if (Which > 16)
    	   {
    		tmpWhich = 16; 
    	   }
        }
	  String colDesc = "";
	  String colNN   = "";
      switch (tmpWhich)
        {
         case  1:
   	       colDesc = "Desc01";
   	       colNN   = "NN01";
   	       break;
         case  2:
   	       colDesc = "Desc02";
   	       colNN   = "NN02";
   	       break;
         case  3:
   	       colDesc = "Desc03";
   	       colNN   = "NN03";
   	       break;
         case  4:
   	       colDesc = "Desc04";
   	       colNN   = "NN04";
   	       break;
         case  5:
   	       colDesc = "Desc05";
   	       colNN   = "NN05";
   	       break;
         case  6:
   	       colDesc = "Desc06";
   	       colNN   = "NN06";
   	       break;
         case  7:
   	       colDesc = "Desc07";
   	       colNN   = "NN07";
   	       break;
         case  8:
   	       colDesc = "Desc08";
   	       colNN   = "NN08";
   	       break;
         case  9:
   	       colDesc = "Desc09";
   	       colNN   = "NN09";
   	       break;
         case 10:
   	       colDesc = "Desc10";
   	       colNN   = "NN10";
   	       break;
         case 11:
   	       colDesc = "Desc11";
   	       colNN   = "NN11";
   	       break;
         case 12:
   	       colDesc = "Desc12";
   	       colNN   = "NN12";
   	       break;
         case 13:
   	       colDesc = "Desc13";
   	       colNN   = "NN13";
   	       break;
         case 14:
   	       colDesc = "Desc14";
   	       colNN   = "NN14";
   	       break;
         case 15:
   	       colDesc = "Desc15";
   	       colNN   = "NN15";
   	       break;
         case 16:
   	       colDesc = "Desc16";
   	       colNN   = "NN16";
   	       break;
        }
	 String upd = "UPDATE nxt_nmbr "             +
                     "SET " + colDesc + " = ?, " +
                              colNN   + " = ?, " +
                          "TMS_Updt       = ?, " +
			              "User_Updt      = ? "  +
                   "WHERE Sys  = ?";
	  try
		{
         DB_Pipe              = DB_Connect();
		 PreparedStatement ps = DB_Pipe.prepareStatement(upd);
	     Timestamp         ts = DateUtils.getTmstmp();
		 ps.setString(1, " ");
		 ps.setInt(2, 0);
		 ps.setTimestamp(3, ts);
		 ps.setString(4, "USER8888");
		 ps.setString(5, tmpSys);
		 ps.executeUpdate();
		 ps.close();
		 return true;
		}
	  catch(SQLException sqle)
		{
		 //sqle.printStackTrace();	// for debugging
	 	 return false;
		}
	  finally
		{
		 DB_Disconnect(DB_Pipe);
		}
	 }

   public boolean addNxtNmbr (NxtNmbr nn)
	 {
	  String ins = "INSERT "                                                                 +
                     "INTO cd_val (Sys, "                                                    +
                                  "Desc01, NN01, Desc02, NN02, Desc03, NN03, Desc04, NN04, " +
                                  "Desc05, NN05, Desc06, NN06, Desc07, NN07, Desc08, NN08, " +
                                  "Desc09, NN09, Desc10, NN10, Desc11, NN11, Desc12, NN12, " +
                                  "Desc13, NN13, Desc14, NN14, Desc15, NN15, Desc16, NN16, " +
                                  "TMS_Crtd, "                                               +
                                  "User_Crtd, "                                              +
                                  "TMS_Updt, "                                               +
                                  "User_Updt) "                                              +
	               "VALUES (?, " +
	                       "?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, " +
	                       "?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, " +
	                       "?, ?, ?, ?)";
	  try
		{
         DB_Pipe              = DB_Connect();
		 PreparedStatement ps = DB_Pipe.prepareStatement(ins);
	     Timestamp         ts = DateUtils.getTmstmp();
		 ps.setString(1, nn.getSys());
		 ps.setString(2, nn.getDesc01());
		 ps.setInt(3, nn.getNN01());
		 ps.setString(4, nn.getDesc02());
		 ps.setInt(5, nn.getNN02());
		 ps.setString(6, nn.getDesc03());
		 ps.setInt(7, nn.getNN03());
		 ps.setString(8, nn.getDesc04());
		 ps.setInt(9, nn.getNN04());
		 ps.setString(10, nn.getDesc05());
		 ps.setInt(11, nn.getNN05());
		 ps.setString(12, nn.getDesc06());
		 ps.setInt(13, nn.getNN06());
		 ps.setString(14, nn.getDesc07());
		 ps.setInt(15, nn.getNN07());
		 ps.setString(16, nn.getDesc08());
		 ps.setInt(17, nn.getNN08());
		 ps.setString(18, nn.getDesc09());
		 ps.setInt(19, nn.getNN09());
		 ps.setString(20, nn.getDesc10());
		 ps.setInt(21, nn.getNN10());
		 ps.setString(22, nn.getDesc11());
		 ps.setInt(23, nn.getNN11());
		 ps.setString(24, nn.getDesc12());
		 ps.setInt(25, nn.getNN12());
		 ps.setString(26, nn.getDesc13());
		 ps.setInt(27, nn.getNN13());
		 ps.setString(28, nn.getDesc14());
		 ps.setInt(29, nn.getNN14());
		 ps.setString(30, nn.getDesc15());
		 ps.setInt(31, nn.getNN15());
		 ps.setString(32, nn.getDesc16());
		 ps.setInt(33, nn.getNN16());
		 ps.setTimestamp(34, ts);
		 ps.setString(35, "USER0001");
		 ps.setTimestamp(36, ts);
		 ps.setString(37, "USER9999");
		 ps.executeUpdate();
		 ps.close();
		 return true;
		}
	  catch(SQLException sqle)
		{
		 //sqle.printStackTrace();	// for debugging
		 return false;
		}
	  finally
		{
		 DB_Disconnect(DB_Pipe);
		}
	 }

   public boolean updateNxtNmbr (NxtNmbr nn)
	 {
	  String upd = "UPDATE nxt_nmbr "         +
                      "SET Desc01    = ?, " +
                          "NN01      = ?, " +
                          "Desc02    = ?, " +
                          "NN02      = ?, " +
                          "Desc03    = ?, " +
                          "NN03      = ?, " +
                          "Desc04    = ?, " +
                          "NN04      = ?, " +
                          "Desc05    = ?, " +
                          "NN05      = ?, " +
                          "Desc06    = ?, " +
                          "NN06      = ?, " +
                          "Desc07    = ?, " +
                          "NN07      = ?, " +
                          "Desc08    = ?, " +
                          "NN08      = ?, " +
                          "Desc09    = ?, " +
                          "NN09      = ?, " +
                          "Desc10    = ?, " +
                          "NN10      = ?, " +
                          "Desc11    = ?, " +
                          "NN11      = ?, " +
                          "Desc12    = ?, " +
                          "NN12      = ?, " +
                          "Desc13    = ?, " +
                          "NN13      = ?, " +
                          "Desc14    = ?, " +
                          "NN14      = ?, " +
                          "Desc15    = ?, " +
                          "NN15      = ?, " +
                          "Desc16    = ?, " +
                          "NN16      = ?, " +
                          "TMS_Updt  = ?, " +
			              "User_Updt = ? "  +
                    "WHERE Sys  = ?";
	  try
		{
         DB_Pipe              = DB_Connect();
		 PreparedStatement ps = DB_Pipe.prepareStatement(upd);
	     Timestamp         ts = DateUtils.getTmstmp();
		 ps.setString(1, nn.getDesc01());
		 ps.setInt(2, nn.getNN01());
		 ps.setString(3, nn.getDesc02());
		 ps.setInt(4, nn.getNN02());
		 ps.setString(5, nn.getDesc03());
		 ps.setInt(6, nn.getNN03());
		 ps.setString(7, nn.getDesc04());
		 ps.setInt(8, nn.getNN04());
		 ps.setString(9, nn.getDesc05());
		 ps.setInt(10, nn.getNN05());
		 ps.setString(11, nn.getDesc06());
		 ps.setInt(12, nn.getNN06());
		 ps.setString(13, nn.getDesc07());
		 ps.setInt(14, nn.getNN07());
		 ps.setString(15, nn.getDesc08());
		 ps.setInt(16, nn.getNN08());
		 ps.setString(17, nn.getDesc09());
		 ps.setInt(18, nn.getNN09());
		 ps.setString(19, nn.getDesc10());
		 ps.setInt(20, nn.getNN10());
		 ps.setString(21, nn.getDesc11());
		 ps.setInt(22, nn.getNN11());
		 ps.setString(23, nn.getDesc12());
		 ps.setInt(24, nn.getNN12());
		 ps.setString(25, nn.getDesc13());
		 ps.setInt(26, nn.getNN13());
		 ps.setString(27, nn.getDesc14());
		 ps.setInt(28, nn.getNN14());
		 ps.setString(29, nn.getDesc15());
		 ps.setInt(30, nn.getNN15());
		 ps.setString(31, nn.getDesc16());
		 ps.setInt(32, nn.getNN16());
		 ps.setTimestamp(33, ts);
		 ps.setString(34, "USER8888");
		 ps.setString(35, nn.getSys());
		 ps.executeUpdate();
		 ps.close();
		 return true;
		}
	  catch(SQLException sqle)
		{
		 //sqle.printStackTrace();	// for debugging
	 	 return false;
		}
	  finally
		{
		 DB_Disconnect(DB_Pipe);
		}
	 }
   
   public boolean deleteNxtNmbr (NxtNmbr nn)
	 {
	  String del = "DELETE "          +
                     "FROM nxt_nmbr " +
                    "WHERE Sys  = ?";
	  try
		{
         DB_Pipe              = DB_Connect();
		 PreparedStatement ps = DB_Pipe.prepareStatement(del);
		 ps.setString(1, nn.getSys());
		 ps.executeUpdate();
		 ps.close();
		 return true;
		}
	  catch(SQLException sqle)
		{
		 //sqle.printStackTrace();	// for debugging
		 return false;
		}
	  finally
		{
		 DB_Disconnect(DB_Pipe);
		}
	 }
  }
